using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
//using System.Windows.Forms;


namespace DB
{
    public class OLEDBAccessor : DBAccessor
    {
        private OleDbConnection connection;

        public OLEDBAccessor()
        {
            connection = new OleDbConnection();
            connection.ConnectionString = Configurations.DB.ConnectionString;
        }

        public override bool Find(string request, TableGenerator.TableGenerator tableType, ref DataTable result)
        {
            try
            {
                string sqlStr = request;

                OleDbDataReader reader;
                OleDbCommand command = new OleDbCommand();
                command.CommandText = sqlStr;
                command.Connection = connection;
                reader = command.ExecuteReader();

                result = tableType.GenerateTable();
                int colCount = result.Columns.Count;
                int index = 0;
                while (reader.Read())
                {
                    DataRow row = result.NewRow();
                    result.Rows.Add(row);
                    for (int i = 0; i < colCount; i++)
                    {
                        result.Rows[index][i] = reader[i];
                    }
                    index++;
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        public override bool Add(string request)
        {
            try
            {
                string sqlInsertString = request;
                OleDbCommand command = new OleDbCommand();
                command.CommandText = sqlInsertString;
                command.Connection = connection;
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        public override bool Delete(string request)
        {
            try
            {
                string sqlStr = request;

                OleDbCommand command = new OleDbCommand();
                command.CommandText = sqlStr;
                command.Connection = connection;
                if (command.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        /// <summary>
        /// Uses ID to find and modify records in DB
        /// </summary>
        /// <param name="recToEdit"></param>
        /// <returns></returns>
        public override bool Modify(string request)
        {
            try
            {
                string sqlStr = request;

                OleDbCommand command = new OleDbCommand();
                command.CommandText = sqlStr;
                command.Connection = connection;
                if (command.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        public override bool Open()
        {
            try
            {
                connection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        public override void Close()
        {
            connection.Close();
        }
    }
}